1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
| public class RevenueRepository
{
private readonly SqlConnection _conn;
private readonly ILogger<RevenueRepository> _logger;
public RevenueRepository(ILogger<RevenueRepository> logger, SqlConnection conn)
{
_logger = logger;
_conn = conn;
}
public IEnumerable<SeasonRevenueIncrease> GetSeasonRevenueIncrease(int year, int season)
{
return _conn.Query<SeasonRevenueIncrease>(
@"select
ThisSeason.*,
stockinfo.stock_name,
LastYearSeason.eps last_year_eps,
(Convert(decimal(10,2),ThisSeason.eps) - Convert(decimal(10,2),LastYearSeason.eps))/Convert(decimal(10,2),ThisSeason.eps)*100 increase_eps_rate
from
(
SELECT
stock_id,
[基本每股盈餘（元）] eps,
Convert(bigint,replace([營業毛利（毛損）淨額],',','')) gross_profit,
Convert(bigint,replace([營業收入],',','')) operating_income
FROM
(SELECT stock_id, item, value FROM SeasonReport
WHERE year =@year and season =@season and type = 1 ) AS SourceTable
PIVOT
(
max(value)
FOR item IN ( [公司名稱], [基本每股盈餘（元）],[營業毛利（毛損）淨額], [營業收入])
) AS temp
) ThisSeason,
(
SELECT
stock_id,
[基本每股盈餘（元）] eps,
Convert(bigint,replace([營業毛利（毛損）淨額],',','')) gross_profit,
Convert(bigint,replace([營業收入],',','')) operating_income
FROM
(SELECT stock_id, item, value FROM SeasonReport
WHERE year =@last_year and season =@season and type = 1 ) AS SourceTable
PIVOT
(
max(value)
FOR item IN ( [公司名稱], [基本每股盈餘（元）],[營業毛利（毛損）淨額], [營業收入])
) AS temp
) LastYearSeason,
(select distinct stock_id, stock_name from TaiwanStockInfo) stockinfo
where
ThisSeason.stock_id = LastYearSeason.stock_id and
ThisSeason.stock_id = stockinfo.stock_id and
ThisSeason.eps > LastYearSeason.eps and
Convert(decimal,ThisSeason.eps) > 0
order by increase_eps_rate desc",
new {
year,
season,
last_year = year - 1
}
);
}
}
|
